package tmts;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import models.Lap;
import models.Skater;

public class DatabaseHandler
{
    Connection conn = null;
    private static final String DB_NAME = "skater_app";
    private static final String USERNAME = "skater";
    private static final String PASSWORD = "skate_password";

    public DatabaseHandler()
    {
        String url = "jdbc:mysql://localhost/" + DB_NAME;
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, USERNAME, PASSWORD);
            System.out.println("Database connection established");
        } catch (InstantiationException e)
        {
            System.out.println("Instantiation exception");
        } catch (IllegalAccessException e)
        {
            System.out.println("Illegal access exception");
        } catch (ClassNotFoundException e)
        {
            System.out.println("Class not found");
        } catch (SQLException e)
        {
            System.out.println("SQL exception");
        }
    }

    public boolean insertLine(Skater skater, Lap lap)
    {
        String query = String
                .format("INSERT INTO `skater_app`.`skater_lines` (`skater_ID`, `name`, `country`, `total_time`, `lap_time`, `distance`) VALUES (%d, '%s', '%s', %d, %d, %d)",
                        skater.getSkaterId(), skater.getSkaterName(), skater.getCountry(), lap.getTime(), lap.getLaptime(),
                        lap.getDistance());

        boolean succeeded = executeQuery(query);

        String query2 = String.format(
                "INSERT INTO `skater_app`.`Lap` (`skaterID`, `raceID`, `distance`, `time`, `laptime`) VALUES (%d, %d, %d, %d, %d)",
                skater.getSkaterId(), skater.getRaceId(), lap.getDistance(), lap.getTime(), lap.getLaptime());

        succeeded = succeeded && executeQuery(query2);

        if (!succeeded)
        {
            Server.debug("Could not insert line");
        }
        return succeeded;

    }

    public boolean startRace(int raceId, int distance, ArrayList<Skater> skaters)
    {
        boolean raceExists = false;
        boolean succeeded = true;

        String selectQuery = String.format("SELECT COUNT(id) as nrOfRaces FROM `skater_app`.`Race` WHERE id = %d", raceId);
        Statement s;
        try
        {
            s = conn.createStatement();
            ResultSet rs = s.executeQuery(selectQuery);
            if (rs.next() && rs.getInt("nrOfRaces") > 0)
            {
                raceExists = true;
            }
            s.close();

            if (!raceExists)
            {
                String query = String.format("INSERT INTO `skater_app`.`Race` (`id`, `distance`) VALUES (%d, %d)", raceId, distance);

                succeeded = succeeded && executeQuery(query);
            }

            String skaterQuery, koppelQuery;
            for (Skater skater : skaters)
            {
                if (!skaterExists(skater.getSkaterId()))
                {
                    skaterQuery = String.format("INSERT INTO `skater_app`.`Skater` (`id`, `name`, `country`) VALUES (%d, '%s', '%s')",
                            skater.getSkaterId(), skater.getSkaterName(), skater.getCountry());
                    succeeded = succeeded && executeQuery(skaterQuery);
                }
                koppelQuery = String.format("INSERT INTO `skater_app`.`Koppel_Race_Skater` (`raceID`, `skaterID`) VALUES (%d, %d)",
                        raceId, skater.getSkaterId());
                succeeded = succeeded && executeQuery(koppelQuery);
            }

            if (!succeeded)
            {
                Server.debug("Could not start race");
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            return false;
        }

        return succeeded;
    }

    private boolean skaterExists(int id)
    {
        String selectQuery = String.format("SELECT COUNT(id) as nrOfSkaters FROM `skater_app`.`Skater` WHERE id = %d", id);
        Statement s;
        try
        {
            s = conn.createStatement();

            ResultSet rs = s.executeQuery(selectQuery);
            if (rs.next() && rs.getInt("nrOfSkaters") > 0)
            {
                return true;
            }
            s.close();
        } catch (SQLException e)
        {
            e.printStackTrace();

            return false;
        }
        return false;

    }

    private boolean executeQuery(String sql)
    {
        Statement s;
        if (conn != null)
        {
            try
            {
                s = conn.createStatement();
                s.executeUpdate(sql);
                s.close();
                return true;
            } catch (SQLException e)
            {
                Server.debug("Could not insert line in table.");
                e.printStackTrace();
                return false;
            }
        }
        return false;
    }

    public boolean emptyDatabase(String tableName)
    {
        Statement s;
        if (conn != null)
        {
            try
            {
                s = conn.createStatement();
                s.executeUpdate("TRUNCATE " + tableName);

                s.close();
                return true;
            } catch (SQLException e)
            {
                System.out.println("Could not empty database.");
                return false;
            }
        }
        return false;
    }
}
